
import sqlalchemy
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker, relationship

#创建链接                数据库类型+要使用的底层sql实现://账号:密码@ip地址/数据库名称                            echo=True会把创建时候的信息打印下来
engine = create_engine('mysql+pymysql://root:qQ1002324991.@1.117.160.157/learn_db', encoding='utf-8')

Base = declarative_base() #生成orm基类

class Customer(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))

    billing_address_id = Column(Integer, ForeignKey('address.id'))  #账单地址
    shipping_address_id = Column(Integer, ForeignKey('address.id')) #邮寄地址

    # 建立关联关系，在这个表里 可以通过student字段查Student表里的字段
    billing_address = relationship('Address', foreign_keys=[billing_address_id])
    shipping_address = relationship('Address', foreign_keys=[shipping_address_id])

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    street = Column(String(64))
    city = Column(String(64))
    state = Column(String(64))
    def __repr__(self):
        return '街道:%s, 市：%s, 省：%s' % (self.street, self.city, self.state)
Base.metadata.create_all(engine) #创建表结构